﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


namespace MLY.Bll
{
    
    public class ReportsGenerator
    {
        
        public static List<Dal.TableWorkOrder> InventoryReport(string CustomerId, string ContractorId)
        {
            List<Dal.TableWorkOrder> list = new List<MLY.Dal.TableWorkOrder>();

            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                list = (from wo in db.TableWorkOrders
                        where (wo.CustomerId.ToString() == CustomerId) &&
                              (wo.ContractorId.ToString() == ContractorId) &&
                              (wo.ActualDelivaryDate == null) &&
                              (wo.originalWoForDist == null)
                        orderby wo.SendDate ascending
                        select wo).ToList();

            }


            return list;
        }

        public static List<Dal.TableWorkOrder> LateOrdersReport(string CustomerId, string ContractorId, string DateType, bool IncludeOpen, bool IncludeClosed)
        {
            List<Dal.TableWorkOrder> list = new List<MLY.Dal.TableWorkOrder>();
            int DaysLate = 7;

            DateTime DeletedDate = new DateTime(2099, 01, 01);

            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {

                if (DateType == "2")
                {
                    list = (from wo in db.TableWorkOrders
                            where (wo.CustomerId.ToString() == CustomerId) &&
                                  (wo.ContractorId.ToString() == ContractorId) &&
                                  ((IncludeOpen && (wo.ActualDelivaryDate == null)) ||
                                   (IncludeClosed && (wo.ActualDelivaryDate != null) && (wo.ActualDelivaryDate != DeletedDate))) &&
                                  (wo.RequestedDelivaryDate.HasValue) &&
                                  (wo.RequestedDelivaryDate.Value.AddDays(DaysLate) < DateTime.Now)
                            orderby wo.RequestedDelivaryDate descending
                            select wo).ToList();


                }
                if (DateType == "3")
                {
                    list = (from wo in db.TableWorkOrders
                            where (wo.CustomerId.ToString() == CustomerId) &&
                                  (wo.ContractorId.ToString() == ContractorId) &&
                                  ((IncludeOpen && (wo.ActualDelivaryDate == null)) ||
                                   (IncludeClosed && (wo.ActualDelivaryDate != null) && (wo.ActualDelivaryDate != DeletedDate))) &&
                                  (wo.PlannedDelivaryDate.HasValue) &&
                                  (wo.PlannedDelivaryDate.Value.AddDays(DaysLate) < DateTime.Now)
                            orderby wo.PlannedDelivaryDate descending
                            select wo).ToList();
                }
            }
            return list;
        }
        
        public static List<Dal.TableWorkOrder> WorkordersByDate(string CustomerId, string ContractorId, DateTime From, DateTime Till, string DateType)
        {
            List<Dal.TableWorkOrder> list = new List<MLY.Dal.TableWorkOrder>();

            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                try
                {
                    if (DateType == "1")
                    {
                        // SendDate
                        list = (from wo in db.TableWorkOrders
                                where wo.SendDate >= From &&
                                      wo.SendDate <= Till &&
                                      (wo.CustomerId.ToString() == CustomerId) &&
                                      (wo.ContractorId.ToString() == ContractorId)
                                orderby wo.SendDate ascending
                                select wo).ToList();

                    }
                    else if (DateType == "2")
                    {
                        // SendDate
                        list = (from wo in db.TableWorkOrders
                                where wo.RequestedDelivaryDate >= From &&
                                      wo.RequestedDelivaryDate <= Till &&
                                      (wo.CustomerId.ToString() == CustomerId) &&
                                      (wo.ContractorId.ToString() == ContractorId)
                                orderby wo.RequestedDelivaryDate ascending
                                select wo).ToList();

                    }
                    else if (DateType == "3")
                    {
                        // PlannedDelivaryDate
                        list = (from wo in db.TableWorkOrders
                                where wo.PlannedDelivaryDate >= From &&
                                      wo.PlannedDelivaryDate <= Till &&
                                      (wo.CustomerId.ToString() == CustomerId) &&
                                      (wo.ContractorId.ToString() == ContractorId)
                                orderby wo.PlannedDelivaryDate ascending
                                select wo).ToList();

                    }
                    
                    else if (DateType == "4")
                    {
                        // SendDate
                        list = (from wo in db.TableWorkOrders
                                where wo.ActualDelivaryDate >= From &&
                                      wo.ActualDelivaryDate <= Till &&
                                      (wo.CustomerId.ToString() == CustomerId) &&
                                      (wo.ContractorId.ToString() == ContractorId)
                                orderby wo.DateFieldB ascending
                                select wo).ToList();

                    }
                }
                catch
                { }
            }
            return list;
        }

        public static List<Dal.TableOrganization> OrganizationsSummary(int OrganizationId)
        {
            List<Dal.TableOrganization> list = new List<MLY.Dal.TableOrganization>();
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                list = (from rel in db.TableOrganizationRelations
                        join Customers in db.TableOrganizations on rel.CustomerId equals Customers.OrganizationId
                        where rel.ContractorId == OrganizationId
                        select Customers).Union
                        (from rel in db.TableOrganizationRelations
                         join Contractors in db.TableOrganizations on rel.ContractorId equals Contractors.OrganizationId
                         where rel.CustomerId == OrganizationId
                         select Contractors).ToList();
                        
            
            }           

            return list;
        }

        public static List<Dal.logTbl> SystemErrorsReport(DateTime StartDate, DateTime EndDate)
        {
            List<Dal.logTbl> lst = new List<Dal.logTbl>();
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                lst = (from log in db.logTbls
                       where log.timeReceived >= StartDate && 
                             log.timeReceived <= EndDate && 
                             log.isError.HasValue && log.isError.Value
                       orderby log.timeReceived descending
                       select log).ToList();               
            }

            return lst;
        }

        public static decimal CanceledWorkOrdersPercent(int CustomerId, int ContractorId)
        {
            decimal Result = 0;
            DateTime CancelDate = DateTime.Parse("2099/01/01");
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                decimal CountTotal = (from order in db.TableWorkOrders
                                  where order.CustomerId == CustomerId &&
                                        order.ContractorId == ContractorId
                                  select order).Count();
                decimal CanceledCount = (from order in db.TableWorkOrders
                                     where order.CustomerId == CustomerId &&
                                           order.ContractorId == ContractorId &&
                                           order.ActualDelivaryDate >= CancelDate
                                     select order).Count();

                if (CountTotal > 0)
                    Result = CanceledCount / CountTotal;
                else
                    Result = 0;
            }


            return Result;
        }


        public static decimal CanceledWorkOrdersPercentByQuantity(int CustomerId, int ContractorId)
        {
            decimal Result = 0;
            DateTime CancelDate = DateTime.Parse("2099/01/01");
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                decimal? CountTotal = (from order in db.TableWorkOrders
                                       where order.CustomerId == CustomerId &&
                                             order.ContractorId == ContractorId
                                       select order.Quantity).Sum();
                decimal? CanceledCount = (from order in db.TableWorkOrders
                                          where order.CustomerId == CustomerId &&
                                                order.ContractorId == ContractorId &&
                                                order.ActualDelivaryDate >= CancelDate
                                          select order.Quantity).Sum();
                if (CountTotal.HasValue && CanceledCount.HasValue && CountTotal > 0)
                    Result = CanceledCount.Value / CountTotal.Value;
                else
                    Result = 0;
            }
            
            return Result;
        }

        public class DispatchReportData
        {
            public string OrganizationName { get; set; }
            public string Product { get; set; }
            public decimal? Disp0 { get; set; }
            public decimal? Disp1 { get; set; }
            public decimal? Disp2 { get; set; }
            public decimal? Disp3 { get; set; }
            public decimal? Disp4 { get; set; }
            public decimal? Disp5 { get; set; }
            public decimal? Disp6 { get; set; }
            public decimal? Disp7 { get; set; }
            public decimal? Disp8 { get; set; }
            public decimal? Disp9 { get; set; }
            public decimal? Disp10 { get; set; }
            public decimal? Disp11 { get; set; }
            public decimal? Disp12 { get; set; }
            public decimal? Disp13 { get; set; }
            public decimal? Disp14 { get; set; }
            public decimal? Disp15 { get; set; }
            public decimal? Disp16 { get; set; }
            public decimal? Disp17 { get; set; }
            public decimal? Disp18 { get; set; }
            public decimal? Disp19 { get; set; }
            public decimal? Disp20 { get; set; }

            public bool HasData()
            {
                return ((Disp0.HasValue ? Disp0.Value : 0) +
                        (Disp1.HasValue ? Disp1.Value : 0) +
                        (Disp2.HasValue ? Disp2.Value : 0) +
                        (Disp3.HasValue ? Disp3.Value : 0) +
                        (Disp4.HasValue ? Disp4.Value : 0) +
                        (Disp5.HasValue ? Disp5.Value : 0) +
                        (Disp6.HasValue ? Disp6.Value : 0) +
                        (Disp7.HasValue ? Disp7.Value : 0) +
                        (Disp8.HasValue ? Disp8.Value : 0) +
                        (Disp9.HasValue ? Disp9.Value : 0) +
                        (Disp10.HasValue ? Disp10.Value : 0) +
                        (Disp11.HasValue ? Disp11.Value : 0) +
                        (Disp12.HasValue ? Disp12.Value : 0) +
                        (Disp13.HasValue ? Disp13.Value : 0) +
                        (Disp14.HasValue ? Disp14.Value : 0) +
                        (Disp15.HasValue ? Disp15.Value : 0) +
                        (Disp16.HasValue ? Disp16.Value : 0) +
                        (Disp17.HasValue ? Disp17.Value : 0) +
                        (Disp18.HasValue ? Disp18.Value : 0) +
                        (Disp19.HasValue ? Disp19.Value : 0) +
                        (Disp20.HasValue ? Disp20.Value : 0) > 0);
            }
        }

        public static List<string> GetOrganizationProducts(int OrganizationId, bool IsClient)
        {
            List<string> lst = new List<string>();
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                lst = (from wo in db.TableWorkOrders
                       where (IsClient && wo.CustomerId == OrganizationId)
                          || (!IsClient && wo.ContractorId == OrganizationId)
                       select wo.ProductDescription).Distinct().ToList();
            }
            return lst;

        }

        public static List<DispatchReportData> DeliveryDispatchReport(int OrganizationId, bool IsClient, int DivisionDate, double Interval, string ProductDesctiption, bool byProduct)
        {
            List<DispatchReportData> lst = new List<DispatchReportData>();
            DateTime Now = DateTime.Now;
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                if (byProduct)
                {
                    lst = (from wo in db.WorkOrdersViews
                           where ((IsClient && (wo.CustomerId == OrganizationId)) || (!IsClient && (wo.ContractorId == OrganizationId)))
                               && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                               && !wo.ActualDelivaryDate.HasValue
                           group wo by new { wo.CustomerCatalougeCode } into grwo
                           select new DispatchReportData
                           {
                               OrganizationName = "",
                               Product = grwo.Key.CustomerCatalougeCode,
                               Disp0 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value < Now.AddDays(Interval)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value < Now.AddDays(Interval)))).Sum(wo => wo.Quantity),

                               Disp1 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 2)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 2)))).Sum(wo => wo.Quantity),
                               Disp2 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 2) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 3)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 2) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 3)))).Sum(wo => wo.Quantity),
                               Disp3 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 3) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 4)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 3) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 4)))).Sum(wo => wo.Quantity),
                               Disp4 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 4) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 5)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 4) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 5)))).Sum(wo => wo.Quantity),
                               Disp5 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 5) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 6)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 5) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 6)))).Sum(wo => wo.Quantity),
                               Disp6 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 6) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 7)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 6) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 7)))).Sum(wo => wo.Quantity),
                               Disp7 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 7) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 8)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 7) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 8)))).Sum(wo => wo.Quantity),
                               Disp8 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 8) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 8)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 8) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 8)))).Sum(wo => wo.Quantity),
                               Disp9 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 9) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 10)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 9) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 10)))).Sum(wo => wo.Quantity),
                               Disp10 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 10) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 11)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 10) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 11)))).Sum(wo => wo.Quantity),
                               Disp11 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 11) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 12)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 11) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 12)))).Sum(wo => wo.Quantity),
                               Disp12 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 12) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 13)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 12) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 13)))).Sum(wo => wo.Quantity),
                               Disp13 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 13) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 14)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 13) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 14)))).Sum(wo => wo.Quantity),
                               Disp14 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 14) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 15)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 14) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 15)))).Sum(wo => wo.Quantity),
                               Disp15 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 15) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 16)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 15) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 16)))).Sum(wo => wo.Quantity),
                               Disp16 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 16) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 17)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 16) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 17)))).Sum(wo => wo.Quantity),
                               Disp17 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 17) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 18)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 17) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 18)))).Sum(wo => wo.Quantity),
                               Disp18 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 18) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 19)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 18) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 19)))).Sum(wo => wo.Quantity),
                               Disp19 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 19) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 20)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 19) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 20)))).Sum(wo => wo.Quantity),
                               Disp20 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 20)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 20)))).Sum(wo => wo.Quantity)
                           }).ToList<DispatchReportData>();
                }
                else
                {


                    lst = (from rel in db.TableOrganizationRelations
                           where rel.CustomerId == OrganizationId
                           select new DispatchReportData
                           {
                               Product = "",
                               OrganizationName = IsClient ?
                                (from org in db.TableOrganizations
                                 where org.OrganizationId == rel.ContractorId
                                 select org.OrganizationName).FirstOrDefault() :
                                (from org in db.TableOrganizations
                                 where org.OrganizationId == rel.CustomerId
                                 select org.OrganizationName).FirstOrDefault(),
                               Disp0 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value < Now.AddDays(Interval)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value < Now.AddDays(Interval)))
                                        select wo.Quantity).Sum(),
                               Disp1 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 2)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 2)))
                                        select wo.Quantity).Sum(),
                               Disp2 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 2) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 3)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 2) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 3)))
                                        select wo.Quantity).Sum(),
                               Disp3 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 3) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 4)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 3) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 4)))
                                        select wo.Quantity).Sum(),
                               Disp4 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 4) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 5)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 4) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 5)))
                                        select wo.Quantity).Sum(),
                               Disp5 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 5) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 6)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 5) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 6)))
                                        select wo.Quantity).Sum(),
                               Disp6 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 6) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 7)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 6) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 7)))
                                        select wo.Quantity).Sum(),
                               Disp7 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 7) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 8)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 7) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 8)))
                                        select wo.Quantity).Sum(),
                               Disp8 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 8) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 9)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 8) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 9)))
                                        select wo.Quantity).Sum(),
                               Disp9 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 9) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 10)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 9) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 10)))
                                        select wo.Quantity).Sum(),
                               Disp10 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 10) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 11)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 10) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 11)))
                                         select wo.Quantity).Sum(),
                               Disp11 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 11) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 12)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 11) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 12)))
                                         select wo.Quantity).Sum(),
                               Disp12 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 12) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 13)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 12) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 13)))
                                         select wo.Quantity).Sum(),
                               Disp13 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 13) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 14)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 13) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 14)))
                                         select wo.Quantity).Sum(),
                               Disp14 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 14) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 15)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 14) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 15)))
                                         select wo.Quantity).Sum(),
                               Disp15 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 15) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 16)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 15) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 16)))
                                         select wo.Quantity).Sum(),
                               Disp16 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 16) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 17)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 16) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 17)))
                                         select wo.Quantity).Sum(),
                               Disp17 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 17) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 18)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 17) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 18)))
                                         select wo.Quantity).Sum(),
                               Disp18 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 18) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 19)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 18) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 19)))
                                         select wo.Quantity).Sum(),
                               Disp19 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 19) && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 20)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 19) && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 20)))
                                         select wo.Quantity).Sum(),
                               Disp20 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 20)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 20)))
                                         select wo.Quantity).Sum()
                           }).ToList<DispatchReportData>();

                }
            
            }

            return lst.Where(d => d.HasData()).ToList<DispatchReportData>(); ;
        }

        public static List<DispatchReportData> FutureInventory(int OrganizationId, bool IsClient, int DivisionDate, double Interval, string ProductDesctiption, bool byProduct)
        {
            List<DispatchReportData> lst = new List<DispatchReportData>();
            DateTime Now = DateTime.Now;
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                if (byProduct)
                {
                    lst = (from wo in db.WorkOrdersViews
                           where ((IsClient && (wo.CustomerId == OrganizationId)) || (!IsClient && (wo.ContractorId == OrganizationId)))
                               && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                               && !wo.ActualDelivaryDate.HasValue
                           group wo by new { wo.CustomerCatalougeCode } into grwo
                           select new DispatchReportData
                           {
                               OrganizationName = "",
                               Product = grwo.Key.CustomerCatalougeCode,
                               Disp0 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval)) : false)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval)) : false)))).Sum(wo => wo.Quantity),
                               Disp1 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 2)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 2)))).Sum(wo => wo.Quantity),
                               Disp2 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 3)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 3)))).Sum(wo => wo.Quantity),
                               Disp3 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 4)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 4)))).Sum(wo => wo.Quantity),
                               Disp4 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 5)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 5)))).Sum(wo => wo.Quantity),
                               Disp5 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 6)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 6)))).Sum(wo => wo.Quantity),
                               Disp6 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 7)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 7)))).Sum(wo => wo.Quantity),
                               Disp7 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 8)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 8)))).Sum(wo => wo.Quantity),
                               Disp8 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 9)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 9)))).Sum(wo => wo.Quantity),
                               Disp9 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 10)) ||
                                                         (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 10)))).Sum(wo => wo.Quantity),
                               Disp10 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 11)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 11)))).Sum(wo => wo.Quantity),
                               Disp11 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 12)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 12)))).Sum(wo => wo.Quantity),
                               Disp12 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 13)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 13)))).Sum(wo => wo.Quantity),
                               Disp13 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 14)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 14)))).Sum(wo => wo.Quantity),
                               Disp14 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 15)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 15)))).Sum(wo => wo.Quantity),
                               Disp15 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 16)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 16)))).Sum(wo => wo.Quantity),
                               Disp16 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 17)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 17)))).Sum(wo => wo.Quantity),
                               Disp17 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 18)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 18)))).Sum(wo => wo.Quantity),
                               Disp18 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 19)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 19)))).Sum(wo => wo.Quantity),
                               Disp19 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 20)) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 20)))).Sum(wo => wo.Quantity),
                               Disp20 = grwo.Where(wo => ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue) ||
                                                          (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue))).Sum(wo => wo.Quantity)

                           }).ToList<DispatchReportData>();
                }
                else
                {

                    lst = (from rel in db.TableOrganizationRelations
                           where rel.CustomerId == OrganizationId
                           select new DispatchReportData
                           {
                               Product = "",
                               OrganizationName = IsClient ?
                                    (from org in db.TableOrganizations
                                     where org.OrganizationId == rel.ContractorId
                                     select org.OrganizationName).FirstOrDefault() :
                                    (from org in db.TableOrganizations
                                     where org.OrganizationId == rel.CustomerId
                                     select org.OrganizationName).FirstOrDefault(),
                               Disp0 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval)) : false)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval)) : false)))
                                        select wo.Quantity).Sum(),

                               Disp1 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 2)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 2)))
                                        select wo.Quantity).Sum(),
                               Disp2 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 2)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 2)))
                                        select wo.Quantity).Sum(),
                               Disp3 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 4)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 4)))
                                        select wo.Quantity).Sum(),
                               Disp4 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 5)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 5)))
                                        select wo.Quantity).Sum(),
                               Disp5 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 6)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 6)))
                                        select wo.Quantity).Sum(),
                               Disp6 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 7)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 7)))
                                        select wo.Quantity).Sum(),
                               Disp7 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 8)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 8)))
                                        select wo.Quantity).Sum(),
                               Disp8 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 9)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 9)))
                                        select wo.Quantity).Sum(),
                               Disp9 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 10)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 10)))
                                        select wo.Quantity).Sum(),
                               Disp10 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 11)) ||
                                                (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 11)))
                                         select wo.Quantity).Sum(),
                               Disp11 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 12)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 12)))
                                         select wo.Quantity).Sum(),
                               Disp12 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 13)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 13)))
                                         select wo.Quantity).Sum(),
                               Disp13 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 14)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 14)))
                                         select wo.Quantity).Sum(),
                               Disp14 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 15)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 15)))
                                         select wo.Quantity).Sum(),
                               Disp15 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 16)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 16)))
                                         select wo.Quantity).Sum(),
                               Disp16 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 17)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 17)))
                                         select wo.Quantity).Sum(),
                               Disp17 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 18)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 18)))
                                         select wo.Quantity).Sum(),
                               Disp18 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 19)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 19)))
                                         select wo.Quantity).Sum(),
                               Disp19 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue && wo.RequestedDelivaryDate.Value <= Now.AddDays(Interval * 20)) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue && wo.PlannedDelivaryDate.Value <= Now.AddDays(Interval * 20)))
                                         select wo.Quantity).Sum(),
                               Disp20 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && wo.RequestedDelivaryDate.HasValue) ||
                                               (DivisionDate == 2 && wo.PlannedDelivaryDate.HasValue))
                                         select wo.Quantity).Sum()
                           }).ToList<DispatchReportData>();
                }
            }

            return lst.Where(d => d.HasData()).ToList<DispatchReportData>(); ;
        }

        public static List<DispatchReportData> OpenMaterialReport(int OrganizationId, bool IsClient, int DivisionDate, double Interval, string ProductDesctiption, bool byProduct)
        {
            List<DispatchReportData> lst = new List<DispatchReportData>();
            DateTime Now = DateTime.Now;
            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                if (byProduct)
                {
                    lst = (from wo in db.WorkOrdersViews
                           where ((IsClient && (wo.CustomerId == OrganizationId)) || (!IsClient && (wo.ContractorId == OrganizationId)))
                               && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                               && !wo.ActualDelivaryDate.HasValue
                           group wo by new { wo.CustomerCatalougeCode } into grwo
                           select new DispatchReportData
                           {
                               OrganizationName = "",
                               Product = grwo.Key.CustomerCatalougeCode,
                               Disp0 = grwo.Sum(wo => wo.Quantity),
                               Disp1 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval)) : true)))).Sum(wo => wo.Quantity),
                               Disp2 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 2)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 2)) : true)))).Sum(wo => wo.Quantity),
                               Disp3 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 3)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 3)) : true)))).Sum(wo => wo.Quantity),
                               Disp4 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 4)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 4)) : true)))).Sum(wo => wo.Quantity),
                               Disp5 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 5)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 5)) : true)))).Sum(wo => wo.Quantity),
                               Disp6 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 6)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 6)) : true)))).Sum(wo => wo.Quantity),
                               Disp7 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 7)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 7)) : true)))).Sum(wo => wo.Quantity),
                               Disp8 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 8)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 8)) : true)))).Sum(wo => wo.Quantity),
                               Disp9 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 9)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 9)) : true)))).Sum(wo => wo.Quantity),
                               Disp10 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 10)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 10)) : true)))).Sum(wo => wo.Quantity),
                               Disp11 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 11)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 11)) : true)))).Sum(wo => wo.Quantity),
                               Disp12 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 12)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 12)) : true)))).Sum(wo => wo.Quantity),
                               Disp13 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 13)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 13)) : true)))).Sum(wo => wo.Quantity),
                               Disp14 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 14)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 14)) : true)))).Sum(wo => wo.Quantity),
                               Disp15 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 15)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 15)) : true)))).Sum(wo => wo.Quantity),
                               Disp16 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 16)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 16)) : true)))).Sum(wo => wo.Quantity),
                               Disp17 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 17)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 17)) : true)))).Sum(wo => wo.Quantity),
                               Disp18 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 18)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 18)) : true)))).Sum(wo => wo.Quantity),
                               Disp19 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 19)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 19)) : true)))).Sum(wo => wo.Quantity),
                               Disp20 = grwo.Where(wo => ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 20)) : true)) ||
                                                         (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 20)) : true)))).Sum(wo => wo.Quantity)

                           }).ToList<DispatchReportData>();
                }
                else
                {

                    lst = (from rel in db.TableOrganizationRelations
                           where rel.CustomerId == OrganizationId
                           select new DispatchReportData
                           {
                               Product = "",
                               OrganizationName = IsClient ?
                                    (from org in db.TableOrganizations
                                     where org.OrganizationId == rel.ContractorId
                                     select org.OrganizationName).FirstOrDefault() :
                                    (from org in db.TableOrganizations
                                     where org.OrganizationId == rel.CustomerId
                                     select org.OrganizationName).FirstOrDefault(),
                               Disp0 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                        select wo.Quantity).Sum(),
                               Disp1 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp2 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 2)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 2)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp3 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 3)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 3)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp4 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 4)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 4)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp5 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 5)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 5)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp6 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 6)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 6)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp7 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 7)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 7)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp8 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 8)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 8)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp9 = (from wo in db.TableWorkOrders
                                        where wo.CustomerId == rel.CustomerId
                                           && wo.ContractorId == rel.ContractorId
                                           && !wo.ActualDelivaryDate.HasValue
                                           && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                           && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 9)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 9)) : true)))
                                        select wo.Quantity).Sum(),
                               Disp10 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 10)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 10)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp11 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 11)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 11)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp12 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 12)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 12)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp13 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 13)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 13)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp14 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 14)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 14)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp15 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 15)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 15)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp16 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 16)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 16)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp17 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 17)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 17)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp18 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 18)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 18)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp19 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 19)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 19)) : true)))
                                         select wo.Quantity).Sum(),
                               Disp20 = (from wo in db.TableWorkOrders
                                         where wo.CustomerId == rel.CustomerId
                                            && wo.ContractorId == rel.ContractorId
                                            && !wo.ActualDelivaryDate.HasValue
                                            && (ProductDesctiption == "All products" || wo.ProductDescription == ProductDesctiption)
                                            && ((DivisionDate == 1 && (wo.RequestedDelivaryDate.HasValue ? (wo.RequestedDelivaryDate.Value >= Now.AddDays(Interval * 20)) : true)) ||
                                               (DivisionDate == 2 && (wo.PlannedDelivaryDate.HasValue ? (wo.PlannedDelivaryDate.Value >= Now.AddDays(Interval * 20)) : true)))
                                         select wo.Quantity).Sum()
                           }).ToList<DispatchReportData>();
                }
            }

            return lst.Where(d => d.HasData()).ToList<DispatchReportData>(); ;
        }
    }
}
